
           with service_site as (
               select max(regexp_replace(province, '市', '')) as province,
                      max(city) as city,
                      service_site_code                         as servicesitecode,
                      max(service_site_name)                         as servicesitename,
                      max(city_code) as city_code,
                      max(province_code) as province_code,
                      max(area_code) as area_code,
                      max(area) as area,
                      max(agent_map.agency_code) as agency_code,
                      max(agent.agency_name) as agency_name,
                      max(cooperate_mode) as cooperate_mode,   --合作方式
                      max(cooperate_company) as cooperate_company, --合作快递公司
                      max(cooperate_network) as cooperate_network  --合作网点code
               from jms_dim.dim_lq_service_site_new_base site
               left join jms_dim.dim_lq_agency_area_mapping_base agent_map
                 on site.city_code = agent_map.area_id
               left join jms_dim.dim_lq_agency_area_base agent
                 on agent_map.agency_code = agent.agency_code
               group by service_site_code
           ) ,

---入库，出库，异常出库
            self_pickup as (
           -- 入库，出库，异常出库  add jixk 20230414是否送货上门
               select billcode,expresscompanycode,statuscode,servicesitecode,dt,1 as isInSiteFlag,deliveryflag,createtime from
               (
               select billcode,expresscompanycode,min(statuscode) as statuscode,servicesitecode,to_date(createtime) as dt,deliveryflag,to_date(createtime) as createtime
               from jms_ods.laiqu_self_pickup
               where dt >=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and dt <='{{ execution_date | cst_ds }}'
                 and to_date(createtime) >=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and to_date(createtime) <='{{ execution_date | cst_ds }}'
                 and to_date(createtime) = dt
               group by billcode,expresscompanycode,servicesitecode,to_date(createtime),deliveryflag,to_date(createtime)
               ) a1
               union all
               select billcode,expresscompanycode,statuscode,servicesitecode,dt,0 as isInSiteFlag,deliveryflag,createtime from
               (
               select billcode,expresscompanycode,statuscode,servicesitecode,to_date(pickuptime) as dt,deliveryflag,to_date(createtime) as createtime
               from
               jms_ods.laiqu_self_pickup
               where dt >=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and dt <='{{ execution_date | cst_ds }}'
                 and to_date(pickuptime) >=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and to_date(pickuptime) <='{{ execution_date | cst_ds }}'
                 and to_date(pickuptime) = dt
               group by billcode,expresscompanycode,statuscode,servicesitecode,to_date(pickuptime),deliveryflag,to_date(createtime)
               ) a2
           ) ,

---当日出库和异常出库
             self_pickup_current as (
           -- 当日出库和异常出库
              select billcode,expresscompanycode,statuscode,servicesitecode,dt
                from jms_ods.laiqu_self_pickup
               where dt>=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and dt<='{{ execution_date | cst_ds }}'
                 and to_date(pickuptime) >=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and to_date(pickuptime) <='{{ execution_date | cst_ds }}'
                 and to_date(createtime) = to_date(pickuptime)      ---当日出库
               group by billcode,expresscompanycode,statuscode,servicesitecode,dt
           ) ,
---T+1
            self_pickup_1 as (
               select billcode,expresscompanycode,statuscode,servicesitecode,createtime
               from (
                   select billcode,expresscompanycode,statuscode,servicesitecode, to_date(createtime) as createtime, row_number() over(partition by billcode,servicesitecode,to_date(createtime) order by pickuptime desc) as rank_number
                   from
                   jms_ods.laiqu_self_pickup
                  where dt >=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and dt <='{{ execution_date | cst_ds }}'
                    and datediff(to_date(pickuptime),to_date(createtime)) <= 1      ---T+1出库
               ) tmp where rank_number=1
           ) ,

---self_pickup_3
            self_pickup_3 as (
           --T+3
               select billcode,expresscompanycode,statuscode,servicesitecode,createtime
               from (
                   select billcode,expresscompanycode,statuscode,servicesitecode,to_date(createtime) as createtime, row_number() over(partition by billcode,servicesitecode,to_date(createtime) order by pickuptime desc) as rank_number
                   from
                   jms_ods.laiqu_self_pickup
                   where dt >=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and dt <='{{ execution_date | cst_ds }}'
                     and datediff(to_date(pickuptime),to_date(createtime)) <= 3      ---T+3出库
               ) tmp where rank_number=1
           ),

---self_pickup_6
            self_pickup_6 as (
           --T+6
               select billcode,expresscompanycode,statuscode,servicesitecode,createtime
               from (
                   select billcode,expresscompanycode,statuscode,servicesitecode,to_date(createtime) as createtime, row_number() over(partition by billcode,servicesitecode,to_date(createtime) order by pickuptime desc) as rank_number
                     from jms_ods.laiqu_self_pickup
                    where dt >=date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and dt <='{{ execution_date | cst_ds }}'
                      and datediff(to_date(pickuptime),to_date(createtime)) <= 6      ---T+6出库
               ) tmp where rank_number=1
           )

           insert overwrite table jms_dm.dm_service_site_out_statistics_dt partition(dt)
           select
               self_pickup.billcode,
               self_pickup.expresscompanycode,
               service_site.province,
               service_site.city,
               self_pickup.servicesitecode,
               service_site.servicesitename,
               self_pickup.statuscode,
               self_pickup_current.billcode as self_pickup_current_billcode,
               self_pickup_current.statuscode as self_pickup_current_statuscode,
               self_pickup_1.billcode as self_pickup_1_billcode,
               self_pickup_1.statuscode as self_pickup_1_statuscode,
               self_pickup_3.billcode as self_pickup_3_billcode,
               self_pickup_3.statuscode as self_pickup_3_statuscode,
               self_pickup_6.billcode as self_pickup_6_billcode,
               self_pickup_6.statuscode as self_pickup_6_statuscode,
               self_pickup.isInSiteFlag as isInSiteFlag,
               service_site.city_code,         --城市code
               service_site.province_code,      --省code
               service_site.agency_code,      --代理区code
               service_site.agency_name,      --代理区名字
               service_site.cooperate_mode,    --合作方式
               service_site.area_code,
               service_site.area,
               service_site.cooperate_company,
               service_site.cooperate_network,  --合作网点code
               self_pickup.deliveryflag,       --是否送货上门
               self_pickup.dt as dt
           from self_pickup  self_pickup
           left join self_pickup_current self_pickup_current
             on self_pickup_current.billcode = self_pickup.billcode and self_pickup.dt=self_pickup_current.dt and self_pickup.isInSiteFlag=1
            and self_pickup_current.servicesitecode = self_pickup.servicesitecode and self_pickup_current.expresscompanycode = self_pickup.expresscompanycode
           left join self_pickup_1 self_pickup_1
             on self_pickup.billcode = self_pickup_1.billcode and self_pickup.isInSiteFlag=1 and self_pickup.createtime = self_pickup_1.createtime
            and self_pickup_1.servicesitecode = self_pickup.servicesitecode and self_pickup_1.expresscompanycode = self_pickup.expresscompanycode
           left join  self_pickup_3 self_pickup_3
             on self_pickup.billcode = self_pickup_3.billcode and self_pickup.isInSiteFlag=1 and self_pickup.createtime = self_pickup_3.createtime
            and self_pickup_3.servicesitecode = self_pickup.servicesitecode and self_pickup_3.expresscompanycode = self_pickup.expresscompanycode
           left join self_pickup_6 self_pickup_6
             on self_pickup.billcode = self_pickup_6.billcode and self_pickup.isInSiteFlag=1 and self_pickup.createtime = self_pickup_6.createtime
            and self_pickup_6.servicesitecode = self_pickup.servicesitecode and self_pickup_6.expresscompanycode = self_pickup.expresscompanycode
           left join service_site service_site
             on self_pickup.servicesitecode = service_site.servicesitecode
           distribute by pmod(hash(billcode), 10)
             ;